﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Windows.Forms;

namespace QLXN.Common
{
    public class SqlInteraction
    {
        public SqlInteraction()
        { }

        public SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter adp;
        

        /// <summary>
        /// Save connection information to config file
        /// </summary>
        /// <param name="sv">server name include instance name</param>
        /// <param name="db">database name</param>
        /// <param name="us">database username</param>
        /// <param name="pw">database password</param>
        /// <rereturns>bool</rereturns>
        public bool Save(string sv, string db, string us, string pw)
        {
            bool flag = false;
            try
            {
                string path = Application.StartupPath + @"\config.hTek";
                Hashtable ht = new Hashtable();
                ht.Add("server", sv);
                ht.Add("database", db);
                ht.Add("username", us);
                ht.Add("password", pw);

                using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    using(StreamWriter sw = new StreamWriter(fs))
                    {
                        BinaryFormatter bf = new BinaryFormatter();
                        bf.Serialize(sw.BaseStream, ht);
                        sw.Close();
                    }
                    fs.Close();
                }
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// Open config file
        /// </summary>
        /// <returns>Hashtable</returns>
        public Hashtable Open()
        {
            Hashtable ht = new Hashtable();
            try
            {
                string path = Application.StartupPath + @"\config.hTek";
                using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Read))
                {
                    using(StreamReader sr = new StreamReader(fs))
                    {
                        BinaryFormatter bf = new BinaryFormatter();
                        ht = (Hashtable)bf.Deserialize(sr.BaseStream);
                        sr.Close();
                    }
                    fs.Close();
                }
            }
            catch (Exception ex)
            { }
            return ht;
        }

        /// <summary>
        /// Check connection to database
        /// </summary>
        /// <param name="sv">server name include instance name</param>
        /// <param name="db">database name</param>
        /// <param name="us">database username</param>
        /// <param name="pw">database password</param>
        /// <rereturns>bool</rereturns>
        public bool Connect(string sv, string db, string us, string pw)
        {
            bool flag = false;
            try
            {
                string s = "Data Source=" + sv + ";Initial Catalog=" + db + ";User ID=" + us + ";Password=" + pw;
                conn = new SqlConnection(s);
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                        conn.Close();
                        flag = true;
                    }
                }
            }
            catch (Exception ex)
            { }
            return flag;
        }

        /// <summary>
        /// Read config file and connect to database
        /// </summary>
        /// <returns>bool</returns>
        public bool Connect()
        {
            bool flag = false;
            try
            {
                Hashtable ht = Open();
                string sv, db, us, pw;
                if (ht.Count > 0)
                {
                    if (ht.ContainsKey("server"))
                    {
                        sv = ht["server"].ToString();
                        if (ht.ContainsKey("database"))
                        {
                            db = ht["database"].ToString();
                            if (ht.ContainsKey("username"))
                            {
                                us = ht["username"].ToString();
                                if (ht.ContainsKey("password"))
                                {
                                    pw = ht["password"].ToString();
                                    flag = Connect(sv, db, us, pw);
                                    conn.Open();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            { }
            return flag;
        }

        /// <summary>
        /// Execute sql query
        /// </summary>
        /// <param name="query">sql query</param>
        /// <returns>bool</returns>
        public bool Execute_Sql(string query)
        {
            bool flag = false;
            try
            {
                Connect();
                cmd = new SqlCommand(query, conn);
                int row = cmd.ExecuteNonQuery();
                if (row > 0)
                    flag = true;
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// Exectute sql query to return data
        /// </summary>
        /// <param name="query">sql query</param>
        /// <returns>DataTable</returns>
        public DataTable Execute_Table(string query)
        {
            DataTable dt = new DataTable();
            try
            {
                Connect();
                cmd = new SqlCommand(query, conn);
                adp = new SqlDataAdapter(cmd);
                adp.Fill(dt);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}